﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Threading.Tasks;
using QQTools.Core.Enums.SendGroupMessage;
using QQTools.Data.Models;
using QQTools.Data.Models.SendGroupMsg;
using QQTools.Logging;
 

namespace QQTools.Data.Repositories.SendGroupMessage
{
    /// <summary>
    /// 发送群消息任务QQ群持久化仓储
    /// </summary>
    public class SendGroupMsgRepository
    {
        private readonly string _connectionString;

        public SendGroupMsgRepository(string connectionString)
        {
            _connectionString = connectionString;
            //InitializeDatabase();
        }

        //private void InitializeDatabase()
        //{
        //    using (var connection = new SQLiteConnection(_connectionString))
        //    {
        //        connection.Open();

        //        var command = new SQLiteCommand(@"
        //            CREATE TABLE IF NOT EXISTS send_group_task (
        //                Id INTEGER PRIMARY KEY AUTOINCREMENT,
        //                QQ TEXT NOT NULL,
        //                SendCount INTEGER DEFAULT 0,
        //                CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
        //                UpdateTime DATETIME DEFAULT CURRENT_TIMESTAMP
        //            )", connection);
        //        command.ExecuteNonQuery();
        //    }
        //}

        /// <summary>
        /// 获取所有记录
        /// </summary>
        public async Task<List<SendGroupMsgModel>> GetAllAsync()
        {
            var list = new List<SendGroupMsgModel>();
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "SELECT * FROM send_group_msg";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                list.Add(MapTo(reader));
                            }
                        }
                    }
                }
                LogManager.Info("GetAllAsync 成功获取所有 SendGroupMsgModels 记录。");
            }
            catch (Exception ex)
            {
                LogManager.Error("GetAllAsync 异常：" + ex);
                throw;
            }
            return list;
        }

        /// <summary>
        /// 根据Id获取记录
        /// </summary>
        public async Task<SendGroupMsgModel> GetByIdAsync(int id)
        {
            SendGroupMsgModel model = null;
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "SELECT * FROM send_group_msg WHERE id = @id";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@id", id);
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            if (await reader.ReadAsync())
                            {
                                model = MapTo(reader);
                            }
                        }
                    }
                }
                LogManager.Info($"GetByIdAsync 成功获取 id={id} 的记录。");
            }
            catch (Exception ex)
            {
                LogManager.Error("GetByIdAsync 异常：" + ex);
                
            }
            return model;
        }

        /// <summary>
        /// 根据QQ获取记录
        /// </summary>
        public async Task<List<SendGroupMsgModel>> GetByQQAsync(string qq)
        {
            var list = new List<SendGroupMsgModel>();
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "SELECT * FROM send_group_msg WHERE qq = @qq";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@qq", qq);
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                list.Add(MapTo(reader));
                            }
                        }
                    }
                }
                LogManager.Info($"GetByQQAsync 成功获取 QQ={qq} 的记录。");
            }
            catch (Exception ex)
            {
                LogManager.Error("GetByQQAsync 异常：" + ex);
                throw;
            }
            return list;
        }

        /// <summary>
        /// 根据Id删除记录
        /// </summary>
        public async Task DeleteAsync(int id)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "DELETE FROM send_group_msg WHERE id = @id";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@id", id);
                        await command.ExecuteNonQueryAsync();
                    }
                }
                LogManager.Info($"DeleteAsync 成功删除 id={id} 的记录。");
            }
            catch (Exception ex)
            {
                LogManager.Error("DeleteAsync 异常：" + ex);
                throw;
            }
        }

        /// <summary>
        /// 清空表中的所有记录
        /// </summary>
        public async Task ClearTableAsync()
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "DELETE FROM send_group_msg";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        await command.ExecuteNonQueryAsync();
                    }
                }
                LogManager.Info("ClearTableAsync 成功清空 send_group_msg 表。");
            }
            catch (Exception ex)
            {
                LogManager.Error("ClearTableAsync 异常：" + ex);
                throw;
            }
        }

        /// <summary>
        /// 更新记录，根据 updateStatus 控制是否更新 status 字段
        /// </summary>
        public async Task UpdateAsync(SendGroupMsgModel model, bool updateStatus = true)
        {
            if (model == null)
            {
                LogManager.Error("UpdateAsync 传入的 model 为 null。");
                return;
            }

            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    using (var transaction = connection.BeginTransaction())
                    {
                        // 如果不更新 status，则不包含 status 参数
                        string sql = updateStatus ?
                            @"UPDATE send_group_msg 
                              SET qq = @qq, group_number = @group_number, message_content = @message_content, 
                                  message_type = @message_type, status = @status, send_time = @send_time, 
                                  retry_count = @retry_count, send_count = @send_count, create_time = @create_time, update_time = @update_time 
                              WHERE id = @id"
                            :
                            @"UPDATE send_group_msg 
                              SET qq = @qq, group_number = @group_number, message_content = @message_content, 
                                  message_type = @message_type, send_time = @send_time, 
                                  retry_count = @retry_count, send_count = @send_count, create_time = @create_time, update_time = @update_time 
                              WHERE id = @id";
                        using (var command = new SQLiteCommand(sql, connection, transaction))
                        {
                            command.Parameters.AddWithValue("@qq", model.QQ);
                            command.Parameters.AddWithValue("@group_number", model.GroupNumber);
                            command.Parameters.AddWithValue("@message_content", model.MessageContent ?? string.Empty);
                            command.Parameters.AddWithValue("@message_type", (int)model.MessageType);
                            if (updateStatus)
                            {
                                command.Parameters.AddWithValue("@status", (int)model.Status);
                            }
                            command.Parameters.AddWithValue("@send_time", model.SendTime);
                            command.Parameters.AddWithValue("@retry_count", model.RetryCount);
                            command.Parameters.AddWithValue("@send_count", model.SendCount);
                            command.Parameters.AddWithValue("@create_time", model.CreateTime);
                            command.Parameters.AddWithValue("@update_time", model.UpdateTime);
                            command.Parameters.AddWithValue("@id", model.Id);
                            await command.ExecuteNonQueryAsync();
                        }
                        transaction.Commit();
                    }
                }
                LogManager.Info($"UpdateAsync 成功更新 id={model.Id} 的记录。");
            }
            catch (Exception ex)
            {
                LogManager.Error("UpdateAsync 异常：" + ex);
                throw;
            }
        }

        /// <summary>
        /// 添加记录
        /// </summary>
        public async Task AddAsync(SendGroupMsgModel model)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    using (var transaction = connection.BeginTransaction())
                    {
                        string sql = @"INSERT INTO send_group_msg 
                                       (qq, group_number, message_content, message_type, status, send_time, retry_count, send_count, create_time, update_time)
                                       VALUES (@qq, @group_number, @message_content, @message_type, @status, @send_time, @retry_count, @send_count, @create_time, @update_time)";
                        using (var command = new SQLiteCommand(sql, connection, transaction))
                        {
                            command.Parameters.AddWithValue("@qq", model.QQ);
                            command.Parameters.AddWithValue("@group_number", model.GroupNumber);
                            command.Parameters.AddWithValue("@message_content", model.MessageContent ?? string.Empty);
                            command.Parameters.AddWithValue("@message_type", (int)model.MessageType);
                            command.Parameters.AddWithValue("@status", (int)model.Status);
                            command.Parameters.AddWithValue("@send_time", model.SendTime);
                            command.Parameters.AddWithValue("@retry_count", model.RetryCount);
                            command.Parameters.AddWithValue("@send_count", model.SendCount);
                            command.Parameters.AddWithValue("@create_time", model.CreateTime);
                            command.Parameters.AddWithValue("@update_time", model.UpdateTime);
                            await command.ExecuteNonQueryAsync();
                        }
                        transaction.Commit();
                    }
                }
                LogManager.Info("AddAsync 成功添加一条 SendGroupMsgModels 记录。");
            }
            catch (Exception ex)
            {
                LogManager.Error("AddAsync 异常：" + ex);
                throw;
            }
        }

        /// <summary>
        /// 根据群号查询记录
        /// </summary>
        public async Task<SendGroupMsgModel> GetByGroupNumberAsync(string groupNumber)
        {
            SendGroupMsgModel model = null;
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "SELECT * FROM send_group_msg WHERE group_number = @group_number";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@group_number", groupNumber);
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            if (await reader.ReadAsync())
                            {
                                model = MapTo(reader);
                            }
                        }
                    }
                }
                LogManager.Info($"GetByGroupNumberAsync 成功获取 group_number={groupNumber} 的记录。");
            }
            catch (Exception ex)
            {
                LogManager.Error("GetByGroupNumberAsync 异常：" + ex);
                throw;
            }
            return model;
        }

        /// <summary>
        /// 添加或更新记录：如果根据 QQ 和 group_number 存在则更新（不更新 status），否则添加
        /// </summary>
        public async Task AddOrUpdateAsync(SendGroupMsgModel model)
        {
            try
            {
                // 根据 QQ 和 group_number 判断记录是否存在
                var exist = await GetByQQAndGroupNumberAsync(model.QQ, model.GroupNumber);
                if (exist != null)
                {
                    // 使用不更新 status 的 UpdateAsync
                    model.Id = exist.Id;
                    await UpdateAsync(model, updateStatus: false);
                    LogManager.Info($"AddOrUpdateAsync 更新 QQ={model.QQ} group_number={model.GroupNumber} 的记录。");
                }
                else
                {
                    await AddAsync(model);
                    LogManager.Info($"AddOrUpdateAsync 添加 QQ={model.QQ} group_number={model.GroupNumber} 的记录。");
                }
            }
            catch (Exception ex)
            {
                LogManager.Error("AddOrUpdateAsync 异常：" + ex);
                throw;
            }
        }

        /// <summary>
        /// 批量插入记录：如果记录已存在（基于 qq 和 group_number），则跳过
        /// </summary>
        /// <param name="models">待插入的 SendGroupMsgModel 集合</param>
        public async Task BulkInsertAsync(List<SendGroupMsgModel> models)
        {
            if (models == null || models.Count == 0)
            {
                LogManager.Info("BulkInsertAsync：传入的记录集合为空，无需插入。");
                return;
            }

            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    using (var transaction = connection.BeginTransaction())
                    {
                        foreach (var model in models)
                        {
                            // 使用 INSERT ... SELECT 结合 WHERE NOT EXISTS 判断记录是否存在
                            string sql = @"
                                INSERT INTO send_group_msg 
                                (qq, group_number, message_content, message_type, status, send_time, retry_count, send_count, create_time, update_time)
                                SELECT @qq, @group_number, @message_content, @message_type, @status, @send_time, @retry_count, @send_count, @create_time, @update_time
                                WHERE NOT EXISTS (
                                    SELECT 1 FROM send_group_msg WHERE qq = @qq AND group_number = @group_number
                             );";

                            using (var command = new SQLiteCommand(sql, connection, transaction))
                            {
                                command.Parameters.AddWithValue("@qq", model.QQ);
                                command.Parameters.AddWithValue("@group_number", model.GroupNumber);
                                command.Parameters.AddWithValue("@message_content", model.MessageContent ?? string.Empty);
                                command.Parameters.AddWithValue("@message_type", (int)model.MessageType);
                                command.Parameters.AddWithValue("@status", (int)model.Status);
                                command.Parameters.AddWithValue("@send_time", model.SendTime);
                                command.Parameters.AddWithValue("@retry_count", model.RetryCount);
                                command.Parameters.AddWithValue("@send_count", model.SendCount);
                                command.Parameters.AddWithValue("@create_time", model.CreateTime);
                                command.Parameters.AddWithValue("@update_time", model.UpdateTime);

                                await command.ExecuteNonQueryAsync();
                            }
                        }
                        transaction.Commit();
                    }
                }
                LogManager.Info("BulkInsertAsync 成功批量插入记录（存在重复记录时跳过）。");
            }
            catch (Exception ex)
            {
                LogManager.Error("BulkInsertAsync 异常：" + ex);
                throw;
            }
        }



        /// <summary>
        /// 获取并锁定下一条待处理记录：查找 status=NotStarted 的记录，并将其状态更新为 Running
        /// </summary>
        public async Task<SendGroupMsgModel> GetAndLockNextSendGroupAsync(string qq)
        {
            SendGroupMsgModel model = null;
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    // 开启事务确保查询和更新原子性
                    using (var transaction = connection.BeginTransaction())
                    {
                        // 查询一条符合条件的记录
                        string selectSql = @"SELECT * FROM send_group_msg 
                                             WHERE qq = @qq AND status = @Status
                                             LIMIT 1";
                        using (var selectCmd = new SQLiteCommand(selectSql, connection, transaction))
                        {
                            selectCmd.Parameters.AddWithValue("@qq", qq);
                            selectCmd.Parameters.AddWithValue("@Status", (int)SendGroupMsgStatus.NotStarted);
                            using (var reader = await selectCmd.ExecuteReaderAsync())
                            {
                                if (await reader.ReadAsync())
                                {
                                    model = MapTo(reader);
                                }
                            }
                        }

                        if (model != null)
                        {
                            // 锁定记录，将 status 更新为 Running
                            string updateSql = @"UPDATE send_group_msg 
                                                 SET status = @running, update_time = @update_time 
                                                 WHERE id = @id";
                            using (var updateCmd = new SQLiteCommand(updateSql, connection, transaction))
                            {
                                updateCmd.Parameters.AddWithValue("@running", (int)SendGroupMsgStatus.Running);
                                updateCmd.Parameters.AddWithValue("@update_time", DateTime.Now);
                                updateCmd.Parameters.AddWithValue("@id", model.Id);
                                await updateCmd.ExecuteNonQueryAsync();
                            }
                        }
                        transaction.Commit();
                    }
                }
                LogManager.Info($"GetAndLockNextSendGroupAsync 成功获取并锁定 QQ={qq} 的下一条待处理记录。");
            }
            catch (Exception ex)
            {
                LogManager.Error("GetAndLockNextSendGroupAsync 异常：" + ex);
                throw;
            }
            return model;
        }

        /// <summary>
        /// 将所有 status 为 Running 的记录重置为 NotStarted
        /// </summary>
        public async Task ResetStatusRunningToNotStartedAsync()
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = @"UPDATE send_group_msg 
                                   SET status = @notStarted, update_time = @update_time 
                                   WHERE status = @running";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@notStarted", (int)SendGroupMsgStatus.NotStarted);
                        command.Parameters.AddWithValue("@running", (int)SendGroupMsgStatus.Running);
                        command.Parameters.AddWithValue("@update_time", DateTime.Now);
                        await command.ExecuteNonQueryAsync();
                    }
                }
                LogManager.Info("ResetStatusRunningToNotStartedAsync 成功将所有 Running 状态的记录重置为 NotStarted。");
            }
            catch (Exception ex)
            {
                LogManager.Error("ResetStatusRunningToNotStartedAsync 异常：" + ex);
                throw;
            }
        }

        /// <summary>
        /// 根据 QQ 和 group_number 更新 status
        /// </summary>
        public async Task UpdateStatusByQQAndGroupNumberAsync(string qq, string groupNumber, SendGroupMsgStatus status)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = @"UPDATE send_group_msg 
                                   SET status = @status, update_time = @update_time 
                                   WHERE qq = @qq AND group_number = @groupNumber";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@status", (int)status);
                        command.Parameters.AddWithValue("@update_time", DateTime.Now);
                        command.Parameters.AddWithValue("@qq", qq);
                        command.Parameters.AddWithValue("@groupNumber", groupNumber);
                        await command.ExecuteNonQueryAsync();
                    }
                }
                LogManager.Info($"UpdateStatusByQQAndGroupNumberAsync 成功更新 QQ={qq} group_number={groupNumber} 的状态为 {status}。");
            }
            catch (Exception ex)
            {
                LogManager.Error("UpdateStatusByQQAndGroupNumberAsync 异常：" + ex);
                throw;
            }
        }

        /// <summary>
        /// 根据 QQ 和 status 查询记录
        /// </summary>
        public async Task<List<SendGroupMsgModel>> GetByQQAndStatusAsync(string qq, SendGroupMsgStatus status)
        {
            var list = new List<SendGroupMsgModel>();
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "SELECT * FROM send_group_msg WHERE qq = @qq AND status = @status";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@qq", qq);
                        command.Parameters.AddWithValue("@status", (int)status);
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                list.Add(MapTo(reader));
                            }
                        }
                    }
                }
                LogManager.Info($"GetByQQAndStatusAsync 成功获取 QQ={qq} 且 status={status} 的记录。");
            }
            catch (Exception ex)
            {
                LogManager.Error("GetByQQAndStatusAsync 异常：" + ex);
                throw;
            }
            return list;
        }

        /// <summary>
        /// 根据 QQ 和 status 查询记录数量
        /// </summary>
        public async Task<int> GetCountByQQAndStatusAsync(string qq, SendGroupMsgStatus status)
        {
            int count = 0;
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "SELECT COUNT(1) FROM send_group_msg WHERE qq = @qq AND status = @status";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@qq", qq);
                        command.Parameters.AddWithValue("@status", (int)status);
                        count = Convert.ToInt32(await command.ExecuteScalarAsync());
                    }
                }
                LogManager.Info($"GetCountByQQAndStatusAsync 成功获取 QQ={qq} 且 status={status} 的记录数量。");
            }
            catch (Exception ex)
            {
                LogManager.Error("GetCountByQQAndStatusAsync 异常：" + ex);
                throw;
            }
            return count;
        }

        /// <summary>
        /// 根据 QQ 获取记录总数
        /// </summary>
        public async Task<int> GetCountByQQAsync(string qq)
        {
            int count = 0;
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "SELECT COUNT(1) FROM send_group_msg WHERE qq = @qq";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@qq", qq);
                        count = Convert.ToInt32(await command.ExecuteScalarAsync());
                    }
                }
                LogManager.Info($"GetCountByQQAsync 成功获取 QQ={qq} 的记录总数。");
            }
            catch (Exception ex)
            {
                LogManager.Error("GetCountByQQAsync 异常：" + ex);
                throw;
            }
            return count;
        }

        /// <summary>
        /// 根据 QQ 获取 status 为 Success 的记录总数
        /// </summary>
        public async Task<int> GetSuccessCountByQQAsync(string qq)
        {
            int count = 0;
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "SELECT COUNT(1) FROM send_group_msg WHERE qq = @qq AND status = @success";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@qq", qq);
                        command.Parameters.AddWithValue("@success", (int)SendGroupMsgStatus.Success);
                        count = Convert.ToInt32(await command.ExecuteScalarAsync());
                    }
                }
                LogManager.Info($"GetSuccessCountByQQAsync 成功获取 QQ={qq} 的成功记录数量。");
            }
            catch (Exception ex)
            {
                LogManager.Error("GetSuccessCountByQQAsync 异常：" + ex);
                throw;
            }
            return count;
        }

        /// <summary>
        /// 根据 QQ 和 group_number 获取记录（辅助方法）
        /// </summary>
        private async Task<SendGroupMsgModel> GetByQQAndGroupNumberAsync(string qq, string groupNumber)
        {
            SendGroupMsgModel model = null;
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "SELECT * FROM send_group_msg WHERE qq = @qq AND group_number = @groupNumber";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@qq", qq);
                        command.Parameters.AddWithValue("@groupNumber", groupNumber);
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            if (await reader.ReadAsync())
                            {
                                model = MapTo(reader);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogManager.Error("GetByQQAndGroupNumberAsync 异常：" + ex);
                throw;
            }
            return model;
        }



        public async void ResetMsgTypeAsync(SendGroupMsgType msgType)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = @"UPDATE send_group_msg 
                                   SET message_type = @message_type, update_time = @update_time 
                                   WHERE 1 = 1";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@message_type", (int)msgType);
                        command.Parameters.AddWithValue("@update_time", DateTime.Now);
                        await command.ExecuteNonQueryAsync();
                    }
                }
                LogManager.Info($"ResetMsgTypeAsync 成功将所有 消息类型 的记录重置为 {msgType.ToString()}");
            }
            catch (Exception ex)
            {
                LogManager.Error($"ResetMsgTypeAsync {msgType.ToString()} 异常：", ex);
            }
        }

        /// <summary>
        /// 根据status 查询记录数量
        /// </summary>
        public async Task<int> GetCountByStatusAsync(SendGroupMsgStatus status)
        {
            int count = 0;
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "SELECT COUNT(1) FROM send_group_msg WHERE status = @status";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@status", (int)status);
                        count = Convert.ToInt32(await command.ExecuteScalarAsync());
                    }
                }
                LogManager.Info($"GetCountByQQAndStatusAsync 成功获取 status={status} 的记录数量。");
            }
            catch (Exception ex)
            {
                LogManager.Error("GetCountByQQAndStatusAsync 异常：" + ex);
            }
            return count;
        }

        public async Task UpdateFaildToNotStart()
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = @"UPDATE send_group_msg 
                                   SET status = @status, update_time = @update_time 
                                   WHERE status=@status_failed";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@status", (int)SendGroupMsgStatus.NotStarted);
                        command.Parameters.AddWithValue("@update_time", DateTime.Now);
                        command.Parameters.AddWithValue("@status_failed", SendGroupMsgStatus.Failed);
                        await command.ExecuteNonQueryAsync();
                    }
                }
                LogManager.Info($"UpdateFaildToNotStart 成功更新 所有失败的重置为正常");
            }
            catch (Exception ex)
            {
                LogManager.Error("UpdateFaildToNotStart 异常：" + ex);

            }
        }

        /// <summary>
        /// 将 SqlDataReader 映射为 SendGroupMsgModels 对象
        /// </summary>
        private SendGroupMsgModel MapTo(System.Data.Common.DbDataReader reader)
        {
            return new SendGroupMsgModel
            {
                Id = Convert.ToInt32(reader["id"]),
                QQ = reader["qq"].ToString(),
                GroupNumber = reader["group_number"].ToString(),
                MessageContent = reader["message_content"] as string,
                MessageType = (SendGroupMsgType)Convert.ToInt32(reader["message_type"]),
                Status = (SendGroupMsgStatus)Convert.ToInt32(reader["status"]),
                SendTime = Convert.ToDateTime(reader["send_time"]),
                RetryCount = Convert.ToInt32(reader["retry_count"]),
                SendCount = Convert.ToInt32(reader["send_count"]),
                CreateTime = Convert.ToDateTime(reader["create_time"]),
                UpdateTime = Convert.ToDateTime(reader["update_time"])
            };
        }
    }
}
